package lab1.dao;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import lab1.db.DatabaseManager;
import lab1.vo.GroupsVO;
import lab1.vo.ValueObject;

/**
 * 
 * @author Arty
 * 
 */

public class GroupsDAO implements DAO {

	public void create(ValueObject vo) {
		DatabaseManager db = new DatabaseManager();
		GroupsVO tab = (GroupsVO) vo;
		StringBuilder sb = new StringBuilder();
		PreparedStatement ps = null;
		Connection con = null;
		try {
			con = db.getConnection();
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		sb.append(" INSERT INTO groups (group_desc, group_name)");
		sb.append(" VALUES(?,?)");

		try {
			ps = con.prepareStatement(sb.toString());
			ps.setString(1,tab.getDescription());
			ps.setString(2, tab.getName());
			ps.executeUpdate();
		} catch (SQLException e) {
			System.out.println("SQL Error: " + e.getMessage());
		}

		try {
			con.close();
		} catch (SQLException e) {
			System.out.println("Connection close error: " + e.getMessage());
		}
	}

	public boolean delete(long i) {
		DatabaseManager db = new DatabaseManager();
		StringBuilder sb = new StringBuilder();
		PreparedStatement ps = null;
		Connection con = null;
		try {
			con = db.getConnection();
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		sb.append(" DELETE FROM groups");
		sb.append(" WHERE group_id=?");

		try {
			ps = con.prepareStatement(sb.toString());
			ps.setInt(1, (int) i);
			ps.executeUpdate();
			return true;
		} catch (SQLException e) {
			System.out.println("SQL Error: " + e.getMessage());
		}
		return false;
	}

	public boolean update(ValueObject vo) {
		if (vo == null)
			return false;
		DatabaseManager db = new DatabaseManager();
		GroupsVO tab = (GroupsVO) vo;
		StringBuilder sb = new StringBuilder();
		PreparedStatement ps = null;
		Connection con = null;
		try {
			con = db.getConnection();
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		sb.append(" UPDATE groups SET");
		sb.append(" group_name=?");
		sb.append(" ,group_desc=?");
		sb.append(" WHERE group_id=?");
		
		try {
			ps = con.prepareStatement(sb.toString());
			ps.setString(1, tab.getName());
			ps.setString(2, tab.getDescription());
			ps.setInt(3, tab.getId());
			
			ps.executeUpdate();
			return true;
		} catch (SQLException e) {
			System.out.println(e.getLocalizedMessage());
			return false;
		}
	}

	public ArrayList<ValueObject> retrieve() {
		ArrayList<ValueObject> arr = new ArrayList<ValueObject>();
		DatabaseManager db = new DatabaseManager();
		StringBuilder sb = new StringBuilder();
		PreparedStatement ps = null;
		Connection con = null;
		try {
			con = db.getConnection();
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		sb.append(" SELECT * FROM groups");

		try {
			ps = con.prepareStatement(sb.toString());
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				GroupsVO tab = new GroupsVO();
				tab.setId(Integer.parseInt(rs.getString("role_id"), 10));
				tab.setName(rs.getString("role_name"));
				tab.setDescription(rs.getString("role_desc"));
				arr.add(tab);
				return arr;
			}
		} catch (SQLException e) {
			System.out.println("SQL Error: " + e.getMessage());
		}

		try {
			con.close();
		} catch (SQLException e) {
			System.out.println("Connection close error: " + e.getMessage());
		}

		return null;
	}

	public ValueObject retrieve(long i) {
		DatabaseManager db = new DatabaseManager();
		GroupsVO tab = null;
		StringBuilder sb = new StringBuilder();
		PreparedStatement ps = null;
		Connection con = null;
		try {
			con = db.getConnection();
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		sb.append(" SELECT * FROM groups WHERE");
		sb.append(" role_id=?");

		try {
			ps = con.prepareStatement(sb.toString());
			ps.setInt(1, (int)i);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				tab.setId(Integer.parseInt(rs.getString("group_id"), 10));
				tab.setName(rs.getString("group_name"));
				tab.setDescription(rs.getString("group_desc"));
				return tab;
			}
		} catch (SQLException e) {
			System.out.println("SQL Error: " + e.getMessage());
		}

		try {
			con.close();
		} catch (SQLException e) {
			System.out.println("Connection close error: " + e.getMessage());
		}

		return null;
	}

}
